using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Reflection;
using DataObject.Linq;
using DataObject.Core;

namespace DataObject.DataAccess
{
    public partial class EmployeeDAO : DataBaseProcessBase<EmployeeEntity, OBSDataContext>
    {
        #region Extensibility Method Declarations
        public void InsertCollection(List<EmployeeEntity> ls)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.EmployeeEntities.InsertAllOnSubmit(ls);
                db.SubmitChanges();
            }
        }
        public void Update(EmployeeEntity item)
		{
			using (OBSDataContext db = new OBSDataContext(base.connectionString))
			{
				db.UpdateEmployee(item.EmployeeID,item.UserId,item.FirstName,item.Lastname,item.Gender,item.CountryID,item.Email,item.AddressLine1,item.AddressLine2,item.City,item.Tel,item.Active);
			}
		}
        public List<EmployeeEntity> GetEmployeeAll()
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.EmployeeEntities.ToList();
            }
        }
        public EmployeeEntity GetEmployeeById(string pEmployeeID)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.EmployeeEntities.SingleOrDefault(a=>a.EmployeeID.Equals(pEmployeeID));
            }
        }
        public List<EmployeeEntity> SelectListItem(string EmployeeID,System.Guid? UserId,string FirstName,string Lastname,bool? Gender,int? CountryID,string Email,string AddressLine1,string AddressLine2,string City,string Tel,bool? Active,HashSet<string> SortClause)
        {
            int Count = 0;
            return SelectListItem(EmployeeID,UserId,FirstName,Lastname,Gender,CountryID,Email,AddressLine1,AddressLine2,City,Tel,Active, null, null, out Count, SortClause);
        }
        public List<EmployeeEntity> SelectListItem(string EmployeeID,System.Guid? UserId,string FirstName,string Lastname,bool? Gender,int? CountryID,string Email,string AddressLine1,string AddressLine2,string City,string Tel,bool? Active,int? Page,int? Row,out int Count,HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = (from table in db.EmployeeEntities select table);
                if (!EmployeeID.Equals(string.Empty))
                    query = query.Where(table => table.EmployeeID.ToLower().Equals(EmployeeID.ToLower()));
                if (UserId.HasValue)
                    query = query.Where(table => table.UserId == UserId.Value);
                if (!FirstName.Equals(string.Empty))
                    query = query.Where(table => table.FirstName.ToLower().Equals(FirstName.ToLower()));
                if (!Lastname.Equals(string.Empty))
                    query = query.Where(table => table.Lastname.ToLower().Equals(Lastname.ToLower()));
                if (Gender.HasValue)
                    query = query.Where(table => table.Gender == Gender.Value);
                if (CountryID.HasValue)
                    query = query.Where(table => table.CountryID == CountryID.Value);
                if (!Email.Equals(string.Empty))
                    query = query.Where(table => table.Email.ToLower().Equals(Email.ToLower()));
                if (!AddressLine1.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine1.ToLower().Equals(AddressLine1.ToLower()));
                if (!AddressLine2.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine2.ToLower().Equals(AddressLine2.ToLower()));
                if (!City.Equals(string.Empty))
                    query = query.Where(table => table.City.ToLower().Equals(City.ToLower()));
                if (!Tel.Equals(string.Empty))
                    query = query.Where(table => table.Tel.ToLower().Equals(Tel.ToLower()));
                if (Active.HasValue)
                    query = query.Where(table => table.Active == Active.Value);
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();
                if (Page.HasValue && Row.HasValue)
                    return query.PageIQuery(Page.Value, Row.Value).ToList();
                return query.ToList();
            }
        }
        #endregion
    
        public static EmployeeDAO CreateInstant()
        {
            return new EmployeeDAO();
        }
        public EmployeeEntity GetEmployeeById(Guid userID)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.EmployeeEntities.SingleOrDefault(a => a.UserId.Equals(userID));
            }
        }
        public List<EmployeeEntity> SelectListItem(string EmployeeID, string Name, string Email, string AddressLine1, string AddressLine2, string Tel, HashSet<string> SortClause)
        {
            int Count = 0;
            return SelectListItem(EmployeeID, Name, Email, AddressLine1, AddressLine2, Tel, null, null, out Count, SortClause);
        }
        public List<EmployeeEntity> SelectListItem(string EmployeeID, string Name, string Email, string AddressLine1, string AddressLine2, string Tel, int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = (from table in db.EmployeeEntities select table);
                if (!EmployeeID.Equals(string.Empty))
                    query = query.Where(table => table.EmployeeID.ToLower().Equals(EmployeeID.ToLower()));
                if (!Name.Equals(string.Empty))
                    query = query.Where(table => (table.FirstName.Contains(Name.ToLower()) || table.Lastname.Contains(Name.ToLower())));
                if (!Email.Equals(string.Empty))
                    query = query.Where(table => table.Email.ToLower().Equals(Email.ToLower()));
                if (!AddressLine1.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine1.ToLower().Equals(AddressLine1.ToLower()));
                if (!AddressLine2.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine2.ToLower().Equals(AddressLine2.ToLower()));
                if (!Tel.Equals(string.Empty))
                    query = query.Where(table => table.Tel.ToLower().Equals(Tel.ToLower()));
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();
                if (Page.HasValue && Row.HasValue)
                    return query.PageIQuery(Page.Value, Row.Value).ToList();
                return query.ToList();
            }
        }
        public List<aspnet_User> GetListaspnet_User()
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = from u in db.aspnet_Users
                            join g in db.EmployeeEntities on u.UserId equals g.UserId into g1
                            from g2 in g1.DefaultIfEmpty()
                            where g2 == null
                            select u;
                return query.ToList();
            }
        }
    }

}
